 /*
	AUTHOR : CHANDRA SEKHAR.M   

	VERSION: 1.0
	
	USAGE  : 1) Work Order Status Vs Subcontractor Commit Date
		(To highlighting the work order steps that are late per Cycle Time, 
		using Min work center with activity Field)

	       : 2) For Report On Stopped Work Orders ( work orders not moving )
	
	EXEC SP_WO_STATUS_VS_VENDOR_COMMIT_DATE_REPORT '0',0,0,'0','0','0','0','0','0','0'
	SELECT * FROM FN_QAD_SEARCH_RESULTS('0',0,0,'0','0','0','0','0','0')
 */
ALTER  PROC SP_WO_STATUS_VS_VENDOR_COMMIT_DATE_REPORT
(
 	@SEARCH_PS_TYPE_NAME 	VARCHAR(50),
	@SEARCH_BASE_PRODUCT_ID	INT,
	@SEARCH_PROCESSSTEP_ID 	INT,
	@CARD_TYPE		VARCHAR(50),
	@PACKAGE_TYPE		VARCHAR(50),
	@MEMORY_SIZE		VARCHAR(50),
	@DATE_TYPE		VARCHAR(20),
	@FROM_DATE		VARCHAR(20),
	@TO_DATE		VARCHAR(20),
	@DEFAULT_PROMPT 	VARCHAR(20)
)
 AS
 BEGIN
 	DECLARE @ITEM_NUMBER 	VARCHAR(50)
   	DECLARE @WORK_ORDER_NO 	VARCHAR(50)
   	DECLARE @COMMIT_DATE 	VARCHAR(20)
   	DECLARE @QTY 		INT
   	DECLARE @OPTICAL 	INT
   	DECLARE @D_A 		INT
   	DECLARE @W_B 		INT
   	DECLARE @MOLD 		INT
 	DECLARE @PLAT 		INT
   	DECLARE @FORM 		INT
   	DECLARE @PACK 		INT
 	DECLARE @LATE_FLAG	BIT
 	DECLARE @MIN_ACTIVITY   VARCHAR(10)
 	DECLARE @REL_DATE	DATETIME
 	DECLARE @PROCESSSTEP_ID BIGINT
	DECLARE @NO_OF_DAYS_IN_STAGE INT
 	DECLARE @CYCLE_TIME     INT
	DECLARE @STOPPED_DAYS	INT
	 	
	DECLARE @PRODUCT_TYPE_ID INT
	DECLARE @BASE_PRODUCT_NAME VARCHAR(50)
	DECLARE @PS_TYPE_ID 	INT
	DECLARE @PS_TYPE_NAME 	VARCHAR(50)
 		
 	DECLARE @WORK_ORDER_CURSOR CURSOR
   	
	DECLARE @SEARCH_RESULT_TABLE TABLE ( ITEM_NUMBER VARCHAR(50), ITEM_DESC TEXT, WORK_ORDER_NO VARCHAR(50), WORK_ORDER_ID INT, QTY_COMPLETED INT, QTY_REJECTED INT, QTY_OPEN INT, ORD_DATE	DATETIME, REL_DATE DATETIME, DUE_DATE DATETIME, WO_STATUS VARCHAR(50), ADAPTOR_DATE VARCHAR(50), TIME_STAMP DATETIME )  

	INSERT INTO @SEARCH_RESULT_TABLE SELECT * FROM FN_QAD_SEARCH_RESULTS(@SEARCH_PS_TYPE_NAME, @SEARCH_BASE_PRODUCT_ID, @SEARCH_PROCESSSTEP_ID, @CARD_TYPE, @PACKAGE_TYPE, @MEMORY_SIZE,@DATE_TYPE, @FROM_DATE, @TO_DATE)
	
   	DECLARE @RESULTS_TABLE TABLE (  PRODUCT_TYPE_ID INT, BASE_PRODUCT_NAME VARCHAR(50),PS_TYPE_ID INT, PS_TYPE_NAME VARCHAR(50),PROCESSSTEP_ID BIGINT,ITEM_NUMBER VARCHAR(50), WORK_ORDER_NO VARCHAR(50), COMMIT_DATE VARCHAR(20), QAD_REL_DATE VARCHAR(20), QTY INT,
   					OPTICAL INT, D_A INT, W_B INT, MOLD INT, PLAT INT, FORM INT, PACK INT, LOSS_QTY INT, ACTIVITY VARCHAR(10),LATE_FLAG BIT, STOPPED_DAYS INT, NO_OF_DAYS_LATE INT )
 
   	SET @WORK_ORDER_CURSOR = CURSOR FOR SELECT 
					CASE 
						WHEN p_type is Null THEN 1
						WHEN p_type = 'BOARD' THEN 2
						WHEN p_type ='KIT' THEN 3
					END AS PRODUCT_TYPE_ID, 
					(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
		 			END 
					) AS BASE_PRODUCT_NAME,
					CASE 
						WHEN PS_TYPE_NAME = 'FAB' THEN 1
						WHEN PS_TYPE_NAME = 'Wafer Store' THEN 2
						WHEN PS_TYPE_NAME = 'SORT' THEN 3
						WHEN PS_TYPE_NAME = 'Die Bank' THEN 4
						WHEN PS_TYPE_NAME = 'Assembly' THEN 5
						WHEN PS_TYPE_NAME = 'TEST' THEN 6
						WHEN PS_TYPE_NAME = 'Class Store' THEN 7
						WHEN PS_TYPE_NAME = 'Mark & Pack' or PS_TYPE_NAME = 'Mark &amp; Pack' THEN 8
						WHEN PS_TYPE_NAME = 'Finished Goods' THEN 9
						ELSE 0
					END AS PS_TYPE_NAME_ID,
					PS_TYPE_NAME, P.PROCESSSTEP_ID, QT.ITEM_NUMBER, QT.WORK_ORDER_NO, CONVERT(VARCHAR,AL.AL_EXPECTED_DATE_OUT, 101 ) AS COMMIT_DATE, QT.QTY_OPEN AS QTY,
 	
 					ISNULL( (SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
 						WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = '2nd Optical Inspection' AND LOT_ID = LT.LOT_ID), 0) AS [2/O],
 		
 					ISNULL( (SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
 						WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Die Attach' AND LOT_ID = LT.LOT_ID), 0) AS [DA],
 	
 					ISNULL( (SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
 						WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Wire Bond' AND LOT_ID = LT.LOT_ID), 0) AS [WB],
 	
 					ISNULL( (SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
 						WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Molding' AND LOT_ID = LT.LOT_ID), 0) AS [MOLD],
 	
 					ISNULL( (SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
 						WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Plating' AND LOT_ID = LT.LOT_ID), 0) AS [PLAT],
 	
 					ISNULL( (SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
 						WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Forming' AND LOT_ID = LT.LOT_ID), 0) AS [FORM],
 	
 					ISNULL( (SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
 						WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Packing' AND LOT_ID = LT.LOT_ID), 0) AS [PACK]
 	
 				FROM LOT_TABLE LT, @SEARCH_RESULT_TABLE QT, PRODUCT P, PROCESS_STEP PS, ASSEMBLY_LOT AL
 	
 				WHERE QT.WORK_ORDER_NO = LT.LOT_NUMBER AND QT.ITEM_NUMBER = P.PART_NUMBER AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID  
 				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID = AL.LOT_ID AND LT.LOT_CURRENT_STATUS = 'Running'
 
 	OPEN @WORK_ORDER_CURSOR 
   	FETCH NEXT FROM @WORK_ORDER_CURSOR INTO @PRODUCT_TYPE_ID, @BASE_PRODUCT_NAME ,@PS_TYPE_ID , @PS_TYPE_NAME, @PROCESSSTEP_ID, @ITEM_NUMBER, @WORK_ORDER_NO, @COMMIT_DATE, @QTY, @OPTICAL, @D_A, @W_B, @MOLD, @PLAT, @FORM, @PACK
   	WHILE @@FETCH_STATUS = 0
   	BEGIN
 		
 		SELECT @REL_DATE = REL_DATE FROM QAD_TABLE WHERE WORK_ORDER_NO = @WORK_ORDER_NO
 		SET @LATE_FLAG = 0

		/* To Find the Minimum Work Center with Activity, If the Qty exists in the particular stage that is the min work center activty field */		 		
 		IF @OPTICAL IS NOT NULL AND @OPTICAL > 0
 		BEGIN
 			IF @MIN_ACTIVITY IS NULL
 			BEGIN
 				SET @MIN_ACTIVITY = '2/O'
 				SELECT @CYCLE_TIME = ISNULL( SUM(AST_CYCLE_TIME), 0) FROM ASSEMBLY_STAGE_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND AST_STAGE_NAME IN ('2nd Optical Inspection')
			END
 		END
 		IF @D_A IS NOT NULL AND @D_A > 0
 		BEGIN
 			IF @MIN_ACTIVITY IS NULL
 			BEGIN
 				SET @MIN_ACTIVITY = 'D/A'
 				SELECT @CYCLE_TIME = ISNULL( SUM(AST_CYCLE_TIME), 0) FROM ASSEMBLY_STAGE_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach' )
			END
 		END
 		IF @W_B IS NOT NULL AND @W_B > 0
 		BEGIN
 			IF @MIN_ACTIVITY IS NULL
 			BEGIN
 				SET @MIN_ACTIVITY = 'W/B'
 				SELECT @CYCLE_TIME = ISNULL( SUM(AST_CYCLE_TIME), 0) FROM ASSEMBLY_STAGE_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach', 'Wire Bond' )
			END
 		END
 		IF @MOLD IS NOT NULL AND @MOLD > 0
 		BEGIN
 			IF @MIN_ACTIVITY IS NULL
 			BEGIN
 				SET @MIN_ACTIVITY = 'MOLD'
 				SELECT @CYCLE_TIME = ISNULL( SUM(AST_CYCLE_TIME), 0) FROM ASSEMBLY_STAGE_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach', 'Wire Bond', 'Molding' )
			END
 		END
 		IF @PLAT IS NOT NULL AND @PLAT > 0
 		BEGIN
 			IF @MIN_ACTIVITY IS NULL
 			BEGIN
 				SET @MIN_ACTIVITY = 'PLAT'
 				SELECT @CYCLE_TIME = ISNULL( SUM(AST_CYCLE_TIME), 0) FROM ASSEMBLY_STAGE_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach', 'Wire Bond', 'Molding', 'Plating' )
			END
 		END
 		IF @FORM IS NOT NULL AND @FORM > 0
 		BEGIN
 			IF @MIN_ACTIVITY IS NULL
 			BEGIN
 				SET @MIN_ACTIVITY = 'FORM'
 				SELECT @CYCLE_TIME = ISNULL( SUM(AST_CYCLE_TIME), 0) FROM ASSEMBLY_STAGE_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach', 'Wire Bond', 'Molding', 'Plating', 'Forming' )
			END
 		END
 		IF @PACK IS NOT NULL AND @PACK > 0
 		BEGIN
 			IF @MIN_ACTIVITY IS NULL
 			BEGIN
 				SET @MIN_ACTIVITY = 'PACK'
 				SELECT @CYCLE_TIME = ISNULL( SUM(AST_CYCLE_TIME), 0) FROM ASSEMBLY_STAGE_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach', 'Wire Bond', 'Molding', 'Plating', 'Forming', 'Packing' )
			END
 		END
		
		/* THIS BLOCK is Executes Only Once, when the Minimum Work center Activity Fileld is Found for each Work Order*/
		IF @MIN_ACTIVITY IS NOT NULL
		BEGIN
				SET @NO_OF_DAYS_IN_STAGE = DATEDIFF( D, DATEADD( d, ISNULL( @CYCLE_TIME, 0) , @REL_DATE ), GETDATE() )

				IF @NO_OF_DAYS_IN_STAGE = 1
				BEGIN
					SET @STOPPED_DAYS = 1 
				END
				IF @NO_OF_DAYS_IN_STAGE = 2
				BEGIN
					SET @STOPPED_DAYS = 2
				END
				IF @NO_OF_DAYS_IN_STAGE = 3
				BEGIN
					SET @STOPPED_DAYS = 3
				END
				IF @NO_OF_DAYS_IN_STAGE > 3
				BEGIN
					SET @STOPPED_DAYS = 4
				END
				-- WHEN the Work order running late Setting the Flag as 1
 				IF DATEADD( d, ISNULL( @CYCLE_TIME, 0) , @REL_DATE ) < CONVERT( VARCHAR, GETDATE() ,101 )
 				BEGIN
 					SET @LATE_FLAG = 1
 				END

 				INSERT INTO @RESULTS_TABLE VALUES(@PRODUCT_TYPE_ID, @BASE_PRODUCT_NAME ,@PS_TYPE_ID , @PS_TYPE_NAME, @PROCESSSTEP_ID,@ITEM_NUMBER, @WORK_ORDER_NO, @COMMIT_DATE, CONVERT(VARCHAR,@REL_DATE,101), @QTY, @OPTICAL, @D_A, @W_B, @MOLD, @PLAT, @FORM, @PACK, @QTY-(@OPTICAL+@D_A+ @W_B+@MOLD+@PLAT+@FORM+@PACK) , @MIN_ACTIVITY,@LATE_FLAG,ISNULL(@STOPPED_DAYS, 0),@NO_OF_DAYS_IN_STAGE )
		END
 		 	
 		SET @MIN_ACTIVITY = NULL
 		SET @CYCLE_TIME   = NULL
		SET @NO_OF_DAYS_IN_STAGE = NULL
		SET @REL_DATE	  = NULL
		SET @STOPPED_DAYS = NULL
 				
 		FETCH NEXT FROM @WORK_ORDER_CURSOR INTO @PRODUCT_TYPE_ID, @BASE_PRODUCT_NAME ,@PS_TYPE_ID , @PS_TYPE_NAME, @PROCESSSTEP_ID, @ITEM_NUMBER, @WORK_ORDER_NO, @COMMIT_DATE, @QTY, @OPTICAL, @D_A, @W_B, @MOLD, @PLAT, @FORM, @PACK
 	END
 	CLOSE @WORK_ORDER_CURSOR
   	DEALLOCATE @WORK_ORDER_CURSOR
 	
 	SELECT * FROM @RESULTS_TABLE ORDER BY COMMIT_DATE
 
 END
 